﻿
using MySql.Data.MySqlClient;
using StudentManagerPro.Model;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Security.Cryptography.X509Certificates;
using System.Text;
using System.Threading.Tasks;
namespace StudentManagerPro.DAL
{
    class StudentManagerSever
    {
        /// <summary>
        /// 获取学生列表
        /// </summary>
        /// <returns>学生列表</returns>
        public static List<StudentsView> GetStus()
        {
            // 1: 初始化创建，存储数据库返回信息列表
            List<StudentsView> list = new List<StudentsView>();
            // 2: 定义sql
            string sql = $"select StudentId,StudentName,Gender,StudentIdNo,Birthday,PhoneNumber,ClassName from students LEFT  JOIN studentclass ON students.ClassId = studentclass.ClassId;";

            var reader = ToolsLibrey.MySqlHelpe.ExecuteReader(sql, null);
            while (reader.Read())
            {
                StudentsView stu = new StudentsView()
                {
                    StudentId = Convert.ToInt32(reader["StudentId"]),
                    StudentName = reader["StudentName"].ToString(),
                    Gender = reader["Gender"].ToString(),
                    StudentIdNo = reader["StudentIdNo"].ToString(),
                    Birthday = DateTime.Parse(reader["Birthday"].ToString()),
                    PhoneNumber = reader["PhoneNumber"].ToString(),
                    ClassName = reader["ClassName"].ToString()
                };
                list.Add(stu);
            }
            reader.Close();
            return list;
        }

        /// <summary>
        /// 获取班级列表
        /// </summary>
        /// <returns></returns>
        public static List<StudentClass> GetStuClass()
        {
            List<StudentClass> list = new List<StudentClass>();
            string sql = $"select * from studentclass;";
            var reader = ToolsLibrey.MySqlHelpe.ExecuteReader(sql, null);
            while (reader.Read())
            {
                StudentClass stuClass = new StudentClass()
                {
                    ClassId = Convert.ToInt32(reader["ClassId"]),

                    ClassName = reader["ClassName"].ToString()
                };
                list.Add(stuClass);
            }

            reader.Close();
            return list;
        }
        public static List<StudentsView> GetStus(int classId)
        {
            // 1: 初始化创建，存储数据库返回信息列表
            List<StudentsView> list = new List<StudentsView>();
            // 2: 定义sql
            string sql = $"select StudentId,StudentName,Gender,StudentIdNo,Birthday,PhoneNumber,ClassName from students LEFT  JOIN studentclass ON students.ClassId = studentclass.ClassId where students.ClassId='{classId}';";

            var reader = ToolsLibrey.MySqlHelpe.ExecuteReader(sql, null);
            while (reader.Read())
            {
                StudentsView stu = new StudentsView()
                {
                    StudentId = Convert.ToInt32(reader["StudentId"]),
                    StudentName = reader["StudentName"].ToString(),
                    Gender = reader["Gender"].ToString(),
                    StudentIdNo = reader["StudentIdNo"].ToString(),
                    Birthday = DateTime.Parse(reader["Birthday"].ToString()),
                    PhoneNumber = reader["PhoneNumber"].ToString(),
                    ClassName = reader["ClassName"].ToString()
                };
                list.Add(stu);
            }
            reader.Close();
            return list;
        }

        #region 根据学生id; 获取学生信息
        public static StudentExt GetStuById(int id)
        {
            StudentExt stu = new StudentExt();
            // 1: 定义:sql 注意:学生表 与班级 关联查询

            string sql = $"SELECT StudentId,StudentName,Gender,Birthday,StudentIdNo,CardNo,StuImage,PhoneNumber,Age,StudentAddress,ClassName";
            sql = sql + $" from students as stu LEFT JOIN studentclass as sc";
            sql = sql + $" on stu.ClassId = sc.ClassId";
            sql = sql + $" WHERE stu.StudentId = {id};";
            Console.WriteLine(sql);
            var reader = ToolsLibrey.MySqlHelpe.ExecuteReader(sql, null);
            while (reader.Read())
            {
                stu.Age = Convert.ToInt32(reader["Age"]);
                stu.StudentId = Convert.ToInt32(reader["StudentId"]);
                stu.StudentName = reader["StudentName"].ToString();
                stu.CardNo = reader["CardNo"].ToString();
                //stu.ClassId = reader["ClassId"].ToString();
                stu.ClassName = reader["ClassName"].ToString();
                stu.Gender = reader["Gender"].ToString();
                stu.PhoneNumber = reader["PhoneNumber"].ToString(); ;
                stu.StudentAddress = reader["StudentAddress"].ToString();
                stu.StudentIdNo = reader["StudentIdNo"].ToString();
                stu.StuImage = reader["StuImage"].ToString();
                stu.Birthday = DateTime.Parse(reader["Birthday"].ToString());
            }
            return stu;
        }
        #endregion
        public static int UpdateStu(Student stu)
        {
            string sql = $"Update students Set StudentName=@StudentName,Gender=@Gender,Birthday=@Birthday," +
                $"StudentIdNo=@StudentIdNo,CardNo=@CardNo,StuImage=@StuImage,PhoneNumber=@PhoneNumber," +
                $"StudentAddress=@StudentAddress,ClassId=@ClassId  where StudentId = @StudentId";


            MySqlParameter[] p =
            {
                 new MySqlParameter("@StudentId",stu.StudentId),
                 new MySqlParameter("@StudentName",stu.StudentName),
                 new MySqlParameter("@Gender",stu.Gender),
                 new MySqlParameter("@Birthday",stu.Birthday),
                 new MySqlParameter("@StudentIdNo",stu.StudentIdNo),
                 new MySqlParameter("@CardNo",stu.CardNo),
                 new MySqlParameter("@StuImage",stu.StuImage),
                 new MySqlParameter("@PhoneNumber",stu.PhoneNumber),
                 new MySqlParameter("@StudentAddress",stu.StudentAddress),
                 new MySqlParameter("@ClassId",stu.ClassId),
    };

            try
            {
                return ToolsLibrey.MySqlHelpe.ExecuteNonQuery(sql, p);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return -1;
            }
        }
        #region 根据学生id删除学生
        public static int DeleteStu(int id)

        {
            int res = -1;
            string sql = $"Delete from students where StudentId='{id}';";
            try
            {
                return ToolsLibrey.MySqlHelpe.ExecuteNonQuery(sql);

            }
            catch (Exception ex)
            {
                return res;
            }


        }
        #endregion
    }
}


